package com.Studen.Dao;

import com.Studen.bean.Score;
import com.Studen.bean.User;
import com.Studen.util.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 这里写和数据库相关的操作
 * 1、jdbc六部曲
 * * 第一步；获取jdbc对象
 * * 第二步：获取数据库连接 写sql语句
 * * 第三步：获取数据库操作对象
 * * 第四步：执行SQL语句
 * * 第五步：处理查询结果
 * * 第六步：关闭资源 先开后关
 */
public class DDLDaoMpl implements DDLDao {
    Jdbc jdbc = new Jdbc();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    @Override
    public void InsertDao(User user, int[] a) {
        /**  判断是否有这位同学 如果有返回给老师：已有这位同学
         *    如果没有进行增肌
         *    用id进行查找是否有这位同学
         *   * */
        String sql1="INSERT INTO user_choose VALUES (?,?,?,?)";
        String sql2="INSERT INTO user_choose VALUES (?,?,?,?)";
        String sql3="INSERT INTO user_choose VALUES (?,?,?,?)";
        String sql4="INSERT INTO user_choose VALUES (?,?,?,?)";
        String sql5="INSERT INTO u_user VALUES(?,?,?,?,1,0)";
        connection= jdbc.Get();
        try {
            statement=connection.prepareStatement(sql1);
            statement.setInt(1,user.getU_id());
            statement.setInt(2,9210);
            statement.setString(3,"Java");
            statement.setInt(4,a[0]);
            statement.executeUpdate();
            statement=connection.prepareStatement(sql2);
            statement.setInt(1,user.getU_id());
            statement.setInt(2,9211);
            statement.setString(3,"Python");
            statement.setInt(4,a[1]);
            statement.executeUpdate();
            statement=connection.prepareStatement(sql3);
            statement.setInt(1,user.getU_id());
            statement.setInt(2,9212);
            statement.setString(3,"数据库");
            statement.setInt(4,a[2]);
            statement.executeUpdate();
            statement=connection.prepareStatement(sql4);
            statement.setInt(1,user.getU_id());
            statement.setInt(2,9213);
            statement.setString(3,"计算机网络");
            statement.setInt(4,a[3]);
            statement.executeUpdate();

            statement=connection.prepareStatement(sql5);
            statement.setInt(1,user.getU_id());
            statement.setString(2, user.getU_pwd());
            statement.setString(3, user.getU_name());
            statement.setInt(4,user.getU_sex());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> getadm(String adm) {
        List<User> list = new ArrayList<>();
        //第一步；获取jdbc文件
        Jdbc jdbc = new Jdbc();
        //第二步：获取数据库连接
        Connection connection = jdbc.Get();
        String sql = "SELECT * FROM `u_user` WHERE u_id=" + adm;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //第三步：获取数据库操作对象
            statement = connection.prepareStatement(sql);
            //第四步：执行SQL语句
            resultSet = statement.executeQuery();
            //第五步：处理查询结果
            if (resultSet.next()) {
                User user = new User();

                int u_id = resultSet.getInt("u_id");
                String u_name = resultSet.getString("u_name");

                user.setU_id(u_id);
                user.setU_name(u_name);

                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;

    }

    @Override
    public List<Score> Update(int x, int id) {
        Score s = new Score();
        connection = jdbc.Get();
        List<Score> list = new ArrayList<>();
        String sql1 = "SELECT u_user.u_id ,u_user.u_name,score,course.c_name  FROM course,user_choose," +
                "u_user WHERE course.c_id=user_choose.c_id  AND " +
                "user_choose.u_id=u_user.u_id AND u_user.u_id=?";
        try {
            statement = connection.prepareStatement(sql1);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String c_name = resultSet.getString("c_name");
                s.setC_name(c_name);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }
        String c_name = s.getC_name();
        System.out.println(c_name);
        String sql2 = "UPDATE user_choose SET score=? WHERE c_name=? and u_id=?";
        try {
            connection = jdbc.Get();
            statement = connection.prepareStatement(sql2);
            statement.setInt(1, x);
            statement.setString(2, c_name);
            statement.setInt(3, id);
            statement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            return show(id);
        }
    }

    @Override
    public List<User> file(User user) {
        List<User> list = new ArrayList<>();
        //第一步；获取jdbc文件
        Jdbc jdbc = new Jdbc();
        //第二步：获取数据库连接
        Connection connection = jdbc.Get();
        String sql = "SELECT * FROM `u_user` WHERE u_id=" + user.getU_id();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //第三步：获取数据库操作对象
            statement = connection.prepareStatement(sql);
            //第四步：执行SQL语句
            resultSet = statement.executeQuery();
            //第五步：处理查询结果
            if (resultSet.next()) {
                int u_id = resultSet.getInt("u_id");
                String u_name = resultSet.getString("u_name");

                user.setU_id(u_id);
                user.setU_name(u_name);

                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public void del(int id) {
        String sql = "UPDATE u_user SET u_isdelete=0 WHERE u_id=" + id;
        try {
            connection = jdbc.Get();
            statement = connection.prepareStatement(sql);
            statement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> Select(String str) {


        List<User> list = new ArrayList();
        //第一步；获取jdbc文件
        connection = jdbc.Get();
        //第二步：获取数据库连接

        String sql = "SELECT * FROM `u_user` WHERE u_name=?";

        try {
            //第三步：获取数据库操作对象
            statement = connection.prepareStatement(sql);
            //第四步：执行SQL语句
            statement.setString(1, str);
            resultSet = statement.executeQuery();
            //第五步：处理查询结果
            while (resultSet.next()) {
                User user = new User();
                int u_id = resultSet.getInt("u_id");
                String u_pwd = resultSet.getString("u_pwd");
                String u_name = resultSet.getString("u_name");
                int u_sex = resultSet.getInt("u_sex");
                int u_role = resultSet.getInt("u_role");
                int u_isDelete = resultSet.getInt("u_isdelete");

                user.setU_id(u_id);
                user.setU_pwd(u_pwd);
                user.setU_name(u_name);
                user.setU_sex(u_sex);
                user.setU_isdelete(u_isDelete);
                user.setU_role(u_role);

                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public List<Score> show(int show) {
        connection = jdbc.Get();
        List<Score> list = new ArrayList<>();
        String sql = "SELECT u_user.u_id ,u_user.u_name,score,course.c_name  FROM course,user_choose," +
                "u_user WHERE course.c_id=user_choose.c_id  AND " +
                "user_choose.u_id=u_user.u_id AND u_user.u_id=?";

        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, show);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                Score s = new Score();

                int u_id = resultSet.getInt("u_id");
                String u_name = resultSet.getString("u_name");
                String c_name = resultSet.getString("c_name");
                int score = resultSet.getInt("score");

                s.setU_id(u_id);
                s.setU_name(u_name);
                s.setC_name(c_name);
                s.setScore(score);

                list.add(s);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

        return list;
    }
}
